package com.easyshopping.persistence;

import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.easyshopping.R;
import com.easyshopping.domain.Currency;
import com.easyshopping.domain.Item;
import com.easyshopping.domain.ItemCategory;
import com.easyshopping.domain.ShoppingList;
import com.easyshopping.domain.Unit;

public class SQLitePersistence implements IPersistence {

	private static final String DATABASE_NAME = "data";
	private static final int DATABASE_VERSION = 15;

	private final Context mCtx;

	private static final String TAG = "SQLitePersistence";

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	public SQLitePersistence(Context context) {
		this.mCtx = context;
	}

	public IPersistence open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public Cursor fetchAllShoppingLists() {

		return mDb.query(ShoppingList.SL, new String[] { ShoppingList.SL_ID,
				ShoppingList.SL_NAME }, null, null, null, null, null);
	}

	public Cursor fetchItemCategories() {
		return mDb.query(ItemCategory.ITEMCATEGORY, new String[] {
				ItemCategory.ITEMCATEGORY_ID, ItemCategory.ITEMCATEGORY_NAME },
				null, null, null, null, null);
	}

	public Cursor fetchUnits() {
		
		return mDb.query(Unit.UNIT, new String[] {Unit.UNIT_ID, Unit.UNIT_NAME}, 
				null, null, null, null, null);
	}
	
	public Cursor fetchCurrencies() {
		
		return mDb.query(Currency.CURRENCY, new String[] {Currency.CURRENCY_ID, Currency.CURRENCY_NAME}, 
				null, null, null, null, null);
	}
	
	public Cursor fetchPartShoppingLists() {

		Calendar calendar = Calendar.getInstance();
		calendar.roll(Calendar.DAY_OF_YEAR, -7);

		return mDb
				.query(ShoppingList.SL, new String[] { ShoppingList.SL_ID,
						ShoppingList.SL_NAME }, ShoppingList.SL_CREATION
						+ " >= ?", new String[] { new Long(calendar
						.getTimeInMillis()).toString() }, null, null, null);
	}

	public Cursor fetchAllItemsOfShoppingList(int slID) {

		return mDb.query(Item.ITEM, new String[] { Item.ITEM_ID,
				Item.ITEM_NAME, Item.ITEM_SL, Item.ITEM_STATE }, Item.ITEM_SL
				+ " = ?", new String[] { new Integer(slID).toString() }, null,
				null, null);
	}

	public Cursor fetchRestItemsOfShoppingList(int slID) {
		
		return mDb.query(Item.ITEM, new String[] { Item.ITEM_ID,
				Item.ITEM_NAME, Item.ITEM_SL, Item.ITEM_STATE }, Item.ITEM_SL
				+ " = ? and " + Item.ITEM_STATE + " = ?", new String[] { new Integer(slID).toString(), "0"}, null,
				null, null);
	}
	
	public boolean isShoppinglistFinished(int slID) {
		
		Cursor c = mDb.query(Item.ITEM, new String[] { Item.ITEM_ID}, Item.ITEM_SL
				+ " = ? and " + Item.ITEM_STATE + " = ?", 
				new String[] { new Integer(slID).toString(), "0"}, null,
				null, null);
		
		return c.getCount() == 0; 
	}
	
	public Cursor fetchAllItemsOfCategory(int cat_id) {
		
		return mDb.query(true, Item.ITEM, new String[] { Item.ITEM_ID,
				Item.ITEM_NAME}, Item.ITEM_CAT
				+ " = ?", new String[] { new Integer(cat_id).toString() }, null,
				null, null, null);
	}
	
	public Cursor fetchItem(int item_id) {
		
		return mDb.query(Item.ITEM, new String[] {Item.ITEM_ID,
				Item.ITEM_NAME, Item.ITEM_AMOUNT, Item.ITEM_PRICE, Item.ITEM_CAT, Item.ITEM_CUR, Item.ITEM_UNIT}, 
				Item.ITEM_ID + " = ?",  new String[] { new Integer(item_id).toString() }, 
				null, null, null);
		
	}
	
	public Cursor fetchItemCategory(int itemCategory_Id) {
		
		return mDb.query(ItemCategory.ITEMCATEGORY, new String[] {ItemCategory.ITEMCATEGORY_ID,
				ItemCategory.ITEMCATEGORY_NAME}, 
				ItemCategory.ITEMCATEGORY_ID + " = ?",  new String[] { new Integer(itemCategory_Id).toString() }, 
				null, null, null);
	}
	
	public void updateItem(int item_id, ContentValues values) {
		
		mDb.update(Item.ITEM, values, Item.ITEM_ID + " = ?", 
				new String[] { new Integer(item_id).toString()});
	}
	
	public void updateShoppinglist(int sl_id, ContentValues values) {
		
		mDb.update(ShoppingList.SL, values, ShoppingList.SL_ID + " = ?", 
				new String[] {new Integer(sl_id).toString()});
	}
	
	public void updateItemCategory(int itemCategory_Id, ContentValues values) {
		
		mDb.update(ItemCategory.ITEMCATEGORY, values, ItemCategory.ITEMCATEGORY_ID + " = ?", 
				new String[] { new Integer(itemCategory_Id).toString()});
	}
	
	public void updateItemState(int item_id, boolean state) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(Item.ITEM_STATE, state);

		mDb.update(Item.ITEM, initialValues, Item.ITEM_ID + " = ?",
				new String[] { new Integer(item_id).toString() });
	}

	public void deleteItem(int item_id) {
		mDb.delete(Item.ITEM, Item.ITEM_ID + " = ?", 
				new String[] {new Integer(item_id).toString()});
	}
	
	public void deleteShoppinglist(int sl_id) {
		
		mDb.beginTransaction();
		try {
			mDb.delete(Item.ITEM, Item.ITEM_SL + " = ?", new String[] {new Integer(sl_id).toString()});
			mDb.delete(ShoppingList.SL, ShoppingList.SL_ID + " = ?", 
					new String[] {new Integer(sl_id).toString()});
			mDb.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mDb.endTransaction();
		}
	}
	
	public void deleteItemCategory(int itemCate_Id) {
		mDb.delete(ItemCategory.ITEMCATEGORY, ItemCategory.ITEMCATEGORY_ID + " = ?", 
				new String[] {new Integer(itemCate_Id).toString()});		
	}
	
	public void saveShoppingList(ShoppingList sl) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(ShoppingList.SL_NAME, sl.getName());
		initialValues.put(ShoppingList.SL_CREATION, sl.getCreationTime()
				.getTime());

		long id = mDb.insert(ShoppingList.SL, null, initialValues);
		sl.setId((int) id);
	}

	public void saveItem(Item item) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(Item.ITEM_NAME, item.getName());
		initialValues.put(Item.ITEM_SL, item.getShoppingList().getId());
		initialValues.put(Item.ITEM_STATE, item.isState());
		initialValues.put(Item.ITEM_CAT, item.getCat_id());
		initialValues.put(Item.ITEM_CUR, item.getCur_id());
		initialValues.put(Item.ITEM_UNIT, item.getUnit_id());

		long id = mDb.insert(Item.ITEM, null, initialValues);
		item.setId((int) id);
	}

	public void saveItemCategory(ItemCategory itemCategory) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(ItemCategory.ITEMCATEGORY_NAME, itemCategory
				.getName());

		long id = mDb.insert(ItemCategory.ITEMCATEGORY, null, initialValues);
		itemCategory.setId((int) id);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		private Context ctx;

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.ctx = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			String[] sqls = getDBSQL(com.easyshopping.R.raw.create_db);
			for (int i = 0; i < sqls.length - 1; i++) {
				db.execSQL(sqls[i] + ";");
			}
			
			db.execSQL("insert into ITEMCATEGORY(name) values('" + ctx.getString(R.string.others) + "');");
			
			String[] units = ctx.getResources().getStringArray(R.array.units);
			for (String unit : units) {
				db.execSQL("insert into UNIT(name) values('" + unit + "');");
			}
			
			String[] currencies = ctx.getResources().getStringArray(R.array.currencies);
			for (String currency : currencies) {
				db.execSQL("insert into CURRENCY(name) values('" + currency + "');");
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");

			String[] sqls = getDBSQL(com.easyshopping.R.raw.drop_db);
			for (int i = 0; i < sqls.length - 1; i++) {
				db.execSQL(sqls[i] + ";");
			}
			onCreate(db);
		}

		private String[] getDBSQL(int resId) {

			String sql;

			try {
				InputStream is = ctx.getResources().openRawResource(resId);
				int size = is.available();

				byte[] buffer = new byte[size];
				is.read(buffer);
				is.close();

				sql = new String(buffer);

				return sql.split(";");
			} catch (IOException e) {
				Log.e(TAG, "sql file not found!");
			}

			return null;
		}

	}

}
